import configparser
import logging
import sys
import numpy as np
import pandas as pd
from sqlalchemy import Integer, UniqueConstraint, create_engine, MetaData, Column, Table, String, TIMESTAMP, Double, inspect,text


# Separate TMK data into two sets: CO2 and CH4.
def separate_data(station_id, q_date):
    station_id = config['STATION']['station_id']
    logger.info('Process Separating TMK data.')
    tm1 = q_date + ' 00:00:00.000'
    tm2 = q_date + ' 23:59:59.999'
    # using mysql DATE() function !!
    query = f"""SELECT * FROM tmk_2401 WHERE DATE(TS)='{q_date}' AND STATION_ID='{station_id}' ORDER BY TS; """
    query = text(query)
    print(query)
    df_tmk_2401 = pd.read_sql_query(query, con=engine.connect())
    #df_tmk_2401 = df_tmk_2401[df_tmk_2401['solenoid_valves'].isin([0,2,3])]
    # keep 2 min
    df_sdt_com = pd.DataFrame()

    # getting  data
    df_sdt_com['STATION_ID'] = df_tmk_2401['STATION_ID']
    df_sdt_com['INST_ID'] = df_tmk_2401['INST_ID']
    df_sdt_com['TS'] = df_tmk_2401['TS']
    df_sdt_com['DATE'] = df_tmk_2401['DATE']
    df_sdt_com['TIME'] = df_tmk_2401['TIME']
    df_sdt_com['DAYS_SINCE_JAN'] = df_tmk_2401['FRAC_DAYS_SINCE_JAN1']
    df_sdt_com['CAVITY_TEMP'] = df_tmk_2401['CavityTemp']
    df_sdt_com['CAVITY_PRESSURE'] = df_tmk_2401['CavityPressure']
    df_sdt_com['DAS_TEMP_(C)'] = df_tmk_2401['DasTemp']
    df_sdt_com['ETALON_TEMP'] = df_tmk_2401['EtalonTemp']
    df_sdt_com['HEATER_CURRENT'] = None
    df_sdt_com['INLET_PROPORTIONAL_VALVE'] = None
    df_sdt_com['OUTLET_PROPORTIONAL_VALVE'] = df_tmk_2401['OutletValve']
    df_sdt_com['SOLENOID_VALVES'] = df_tmk_2401['solenoid_valves']
    df_sdt_com['MULTI_POS_VALVE'] = df_tmk_2401['MPVPosition']
    df_sdt_com['SPECTRUMID'] = None
    df_sdt_com['CO2'] = df_tmk_2401['CO2']
    df_sdt_com['CO2_CORR'] = df_tmk_2401['CO2_dry']
    df_sdt_com['CH4'] = df_tmk_2401['CH4']
    df_sdt_com['CH4_CORR'] = df_tmk_2401['CH4_dry']
    df_sdt_com['CO'] = df_tmk_2401['CO']
    df_sdt_com['H2O'] = df_tmk_2401['H2O']

    return df_sdt_com


def add_sample_type(df):
    logger.info('adding sample type to the dataFrame')
    """     mapping = {
        1: 'L1',
        2: 'L2',
        3: 'L3',
        4: 'WH',
        5: 'T',
        6: 'WL'
    }
    df['SampleType'] = df['MULTI_POS_VALVE'].map(mapping) 
    """
    mapping = {
        0: 'A',
        2: 'W',
        3: 'T'
    }
    df['SampleType'] = df['SOLENOID_VALVES'].map(mapping)
    return df


def QCMarking(df):
    logger.info('Marking QC tags')
    df_five = pd.DataFrame(columns=['TS','SampleType','CO2_MEAN','CO2_STD_DEV','CO2_COUNT','CH4_MEAN','CH4_STD_DEV','CH4_COUNT','CO_MEAN','CO_STD_DEV','CO_COUNT','H2O_MEAN'])
    # add 4*3 new columns
    df['QC_co2_1o'] = None
    df['QC_co2_3o'] = 'V'
    df['QC_co2_5o'] = None
    df['QC_co2_10o'] = None
    df['QC_ch4_1o'] = None
    df['QC_ch4_3o'] = 'V'
    df['QC_ch4_5o'] = None
    df['QC_ch4_10o'] = None
    df['QC_co_1o'] = None
    df['QC_co_3o'] = 'V'
    df['QC_co_5o'] = None
    df['QC_co_10o'] = None

    current_group = []
    current_start_time = df['TS'].iloc[0]
    current_sample_type = df['SampleType'].iloc[0]
    station_id = df['STATION_ID'].iloc[0]
    inst_id = df['INST_ID'].iloc[0]

    for _,row in df.iterrows():
        current_time = row['TS']
        current_type = row['SampleType']
        # Check if 5 minutes have passed or the valve position has changed
        if (current_time - current_start_time).total_seconds() >= 300 or current_type != current_sample_type:
           # Compute mean for the current group
           df_current = pd.DataFrame(current_group)
           if len(df_current) > 5:
             result_five = compute_current(df_current)
             df_five = df_five._append(result_five, ignore_index=True)

           # Reset current group
           current_group = []
           current_start_time = current_time
           current_sample_type = current_type

        # Add the current row to the group
        current_group.append(row)
    
    # Handle the last group
    if current_group:
        group_df = pd.DataFrame(current_group)
        if len(group_df) > 5:
          result_five = compute_current(group_df)
          df_five = df_five._append(result_five, ignore_index=True)

    return df, df_five

def compute_current(df_every_5_minutes):
        # Find the start time for the last 2 minutes
        end_time = df_every_5_minutes['TS'].max()
        end_type = df_every_5_minutes['SampleType'].iloc[-1]
        print("end time and type %s %s" % (end_time, end_type))
        start_time = end_time - pd.Timedelta(minutes=2)

        # Filter the DataFrame for the last 2 minutes
        df_every_last_2_minutes = df_every_5_minutes[df_every_5_minutes["TS"] > start_time]
        df_every_5_minutes = df_every_5_minutes.iloc[:-2]
        df_every_last_2_minutes = df_every_last_2_minutes.iloc[:-2]

        if True:   
            if True: #gas_type == 'co2'
              co2_corr_mean = df_every_last_2_minutes['CO2_CORR'].mean()
              co2_corr_std_dev = df_every_last_2_minutes['CO2_CORR'].std()
              if not pd.isna(co2_corr_mean) and not pd.isna(co2_corr_std_dev):
                df_every_5_minutes.loc[(df_every_5_minutes['CO2_CORR'] - co2_corr_mean).abs() > co2_corr_std_dev, 'QC_co2_1o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO2_CORR'] - co2_corr_mean).abs() > 3 * co2_corr_std_dev, 'QC_co2_3o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO2_CORR'] - co2_corr_mean).abs() > 5 * co2_corr_std_dev, 'QC_co2_5o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO2_CORR'] - co2_corr_mean).abs() > 10 * co2_corr_std_dev, 'QC_co2_10o'] = 'I'
                df_every_last_2_minutes.loc[(df_every_last_2_minutes['CO2_CORR'] - co2_corr_mean).abs() > 3 * co2_corr_std_dev, 'QC_co2_3o'] = 'I'
                
            if True: #gas_type == 'ch4'
              ch4_corr_mean = df_every_last_2_minutes['CH4_CORR'].mean()
              ch4_corr_std_dev = df_every_last_2_minutes['CH4_CORR'].std()
              if not pd.isna(ch4_corr_std_dev) and not pd.isna(ch4_corr_mean):
                df_every_5_minutes.loc[(df_every_5_minutes['CH4_CORR'] - ch4_corr_mean).abs() > ch4_corr_std_dev, 'QC_ch4_1o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CH4_CORR'] - ch4_corr_mean).abs() > 3 * ch4_corr_std_dev, 'QC_ch4_3o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CH4_CORR'] - ch4_corr_mean).abs() > 5 * ch4_corr_std_dev, 'QC_ch4_5o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CH4_CORR'] - ch4_corr_mean).abs() > 10 * ch4_corr_std_dev, 'QC_ch4_10o'] = 'I'
                df_every_last_2_minutes.loc[(df_every_last_2_minutes['CH4_CORR'] - ch4_corr_mean).abs() > 3 * ch4_corr_std_dev, 'QC_ch4_3o'] ='I'
                
            if True: #gas_type == 'co'
              co_mean = df_every_last_2_minutes['CO'].mean()
              co_std_dev = df_every_last_2_minutes['CO'].std()
              if not pd.isna(co_std_dev) and not pd.isna(co_mean):
                df_every_5_minutes.loc[(df_every_5_minutes['CO'] - co_mean).abs() > co_std_dev, 'QC_co_1o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO'] - co_mean).abs() > 3 * co_std_dev, 'QC_co_3o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO'] - co_mean).abs() > 5 * co_std_dev, 'QC_co_5o'] = 'I'
                df_every_5_minutes.loc[(df_every_5_minutes['CO'] - co_mean).abs() > 10 * co_std_dev, 'QC_co_10o'] = 'I'
                df_every_last_2_minutes.loc[(df_every_last_2_minutes['CO'] - co_mean).abs() > 3 * co_std_dev, 'QC_co_3o'] = 'I'
                
            row_five = {}
            row_five['TS'] = end_time
            row_five['SampleType'] = end_type
            row_five['CO2_MEAN'] = co2_corr_mean
            row_five['CO2_STD_DEV'] = co2_corr_std_dev
            row_five['CO2_COUNT'] = df_every_last_2_minutes['QC_co2_3o'].value_counts().get('V',0)
            row_five['CH4_MEAN'] = ch4_corr_mean
            row_five['CH4_STD_DEV'] = ch4_corr_std_dev
            row_five['CH4_COUNT'] = df_every_last_2_minutes['QC_ch4_3o'].value_counts().get('V',0)
            row_five['CO_MEAN'] = co_mean
            row_five['CO_STD_DEV'] = co_std_dev
            row_five['CO_COUNT'] = df_every_last_2_minutes['QC_co_3o'].value_counts().get('V',0)
            row_five['H2O_MEAN'] = df_every_last_2_minutes['H2O'].mean()
            return row_five


def create_com_table():
    metadata = MetaData()
    # 定义表结构
    co2_table = Table('sdt_com', metadata,
                      Column('id', Integer, primary_key=True, autoincrement=True),
                      Column('STATION_ID', String(10)),
                      Column('INST_ID', String(10)),
                      Column('TS', TIMESTAMP(6)),
                      Column('DATE', String(16)),
                      Column('TIME', String(16)),
                      Column('DAYS_SINCE_JAN', Double),
                      Column('CAVITY_TEMP', Double),
                      Column('CAVITY_PRESSURE', Double),
                      Column('DAS_TEMP_(C)', Double),
                      Column('ETALON_TEMP', Double),
                      Column('HEATER_CURRENT', Double),
                      Column('INLET_PROPORTIONAL_VALVE', Double),
                      Column('OUTLET_PROPORTIONAL_VALVE', Double),
                      Column('SOLENOID_VALVES', Double),
                      Column('MULTI_POS_VALVE', Double),
                      Column('SPECTRUMID', Double),
                      Column('CO2', Double),
                      Column('CO2_CORR', Double),
                      Column('CH4', Double),
                      Column('CH4_CORR', Double),
                      Column('CO', Double),
                      Column('H2O', Double),
                      Column('SampleType', String(2)),
                      Column('QC_co2_1o', String(1)),
                      Column('QC_co2_3o', String(1)),
                      Column('QC_co2_5o', String(1)),
                      Column('QC_co2_10o', String(1)),
                      Column('QC_ch4_1o', String(1)),
                      Column('QC_ch4_3o', String(1)),
                      Column('QC_ch4_5o', String(1)),
                      Column('QC_ch4_10o', String(1)),
                      Column('QC_co_1o', String(1)),
                      Column('QC_co_3o', String(1)),
                      Column('QC_co_5o', String(1)),
                      Column('QC_co_10o', String(1)),
                      UniqueConstraint("STATION_ID", "INST_ID", "TS", name="sdt_com_uix"))
                      
    # 创建表
    metadata.create_all(engine)

def create_five_table():
    metadata = MetaData()
    # 定义表结构
    five_table = Table('sdt_five', metadata,
                      Column('id', Integer, primary_key=True, autoincrement=True),
                      Column('STATION_ID', String(10)),
                      Column('INST_ID', String(10)),
                      Column('TS', TIMESTAMP(6)),
                      Column('CO2_MEAN', Double),
                      Column('CO2_STD_DEV', Double),
                      Column('CO2_COUNT', Double),
                      Column('CH4_MEAN', Double),
                      Column('CH4_STD_DEV', Double),
                      Column('CH4_COUNT', Double),
                      Column('CO_MEAN', Double),
                      Column('CO_STD_DEV', Double),
                      Column('CO_COUNT', Double),
                      Column('H2O_MEAN', Double),
                      Column('SampleType', String(2)),
                      UniqueConstraint("STATION_ID", "INST_ID", "TS", name="sdt_five_uix"))
    # 创建表
    metadata.create_all(engine)

if __name__ == '__main__':
    config = configparser.ConfigParser()
    config.read('config.ini')
    # Extract log file path
    log_file_path = config['LOGGING']['log_file_path']
    # Configure logging
    logging.basicConfig(filename=log_file_path, level=logging.INFO)
    # Get logger instance
    logger = logging.getLogger(__name__)

    # Extract database connection parameters
    db_engine = config['DATABASE']['engine']
    db_host = config['DATABASE']['host']
    db_port = config['DATABASE']['port']
    db_name = config['DATABASE']['name']
    db_user = config['DATABASE']['user']
    db_password = config['DATABASE']['password']
    station_id = config['STATION']['station_id']
    # Construct the database URL
    db_url = f'{db_engine}://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'

    engine = create_engine(db_url)
    metadata = MetaData()
    inspector = inspect(engine)
    if (inspector.has_table('sdt_com')):
        logger.info("Table sdt_com exists in the database.")
        rdt_table = Table('sdt_com', metadata, autoload_with=engine)
    else:
        logger.info("creating table sdt_com...")
        create_com_table()
        logger.info('created table sdt_com successfully!')

    if (inspector.has_table('sdt_five')):
        logger.info("Table sdt_five exists in the database.")
        rdt_table = Table('sdt_five', metadata, autoload_with=engine)
    else:
        logger.info("creating table sdt_five...")
        create_five_table()
        logger.info('created table sdt_five successfully!')

    date_time = sys.argv[1]
    df_com = separate_data(station_id, date_time)
    df_com = add_sample_type(df_com)
    

    df_co2, df_stats = QCMarking(df_com)
    # add two columns
    df_stats['STATION_ID'] = station_id
    df_stats['INST_ID'] = config['STATION']['inst_id']
    # upload data to database
    logger.info('Uploading data to database')
    
    df_co2.to_sql('sdt_com', engine, index=False, if_exists='append')
    df_stats.to_sql('sdt_five', engine, index=False, if_exists='append')
    logger.info('Done!')